package it.trekker.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {
	
	// Log tag
	private static final String TAG = DatabaseHelper.class.getSimpleName();
	
	// Attributes
	private static final String DATABASE_NAME = "trekker_db";
	private static final int DATABASE_VERSION = 1;
	
	// SQL
	private static final String SQL_CREATE_EXCURSION_TABLE =
			"create table " + ExcursionTableMeta.DATABASE_TABLE + "(" +
					ExcursionTableMeta.KEY_ROWID + " integer primary key autoincrement, " +
					ExcursionTableMeta.KEY_NAME + " text, " +
//					ExcursionTableMeta.KEY_DATE + " datetime default CURRENT_DATE, " +
					ExcursionTableMeta.KEY_DATE + " datetime default (datetime('now', 'localtime')), " +
					ExcursionTableMeta.KEY_STEPS + " integer default 0, " +
					ExcursionTableMeta.KEY_DISTANCE + " real default 0, " +
					ExcursionTableMeta.KEY_MEANPACE + " real default 0, " +
					ExcursionTableMeta.KEY_TOTALTITUDEDIFF + " real default 0, " +
					ExcursionTableMeta.KEY_TIME + " integer default 0" +
			");";
	private static final String SQL_CREATE_EXCURSIONCOORD_TABLE =
			"create table " + ExcursionCoordinateTableMeta.DATABASE_TABLE + "(" +
					ExcursionCoordinateTableMeta.KEY_ROWID + " integer primary key autoincrement, " +
//					GPSCoordinateTableMeta.KEY_TIME + " datetime default CURRENT_TIMESTAMP, " +
					ExcursionCoordinateTableMeta.KEY_TIME + " datetime default (datetime('now', 'localtime')), " +
					ExcursionCoordinateTableMeta.KEY_LATITUDE + " real not null, " +
					ExcursionCoordinateTableMeta.KEY_LONGITUDE + " real not null, " +
					ExcursionCoordinateTableMeta.KEY_ALTITUDE + " real not null, " +
					ExcursionCoordinateTableMeta.KEY_EXCURSIONID + " long not null, " +
					"FOREIGN KEY(" + ExcursionCoordinateTableMeta.KEY_EXCURSIONID + ") REFERENCES " + ExcursionTableMeta.DATABASE_TABLE + "(" + ExcursionTableMeta.KEY_ROWID + ") ON DELETE CASCADE ON UPDATE CASCADE" +
			");";
	private static final String SQL_CREATE_PHOTO_TABLE =
			"create table " + PhotoTableMeta.DATABASE_TABLE + "(" +
					PhotoTableMeta.KEY_ROWID + " integer primary key autoincrement, " +
					PhotoTableMeta.KEY_PATH + " text not null, " +
					PhotoTableMeta.KEY_LATITUDE + " real not null, " +
					PhotoTableMeta.KEY_LONGITUDE + " real not null, " +
					PhotoTableMeta.KEY_EXCURSIONID + " long not null, " +
					"FOREIGN KEY(" + PhotoTableMeta.KEY_EXCURSIONID + ") REFERENCES " + ExcursionTableMeta.DATABASE_TABLE + "(" + ExcursionTableMeta.KEY_ROWID + ") ON DELETE CASCADE ON UPDATE CASCADE" +
			");";
	private static final String SQL_CREATE_POI_TABLE =
			"create table " + PoiTableMeta.DATABASE_TABLE + "(" +
					PoiTableMeta.KEY_ROWID + " integer primary key autoincrement, " +
					PoiTableMeta.KEY_LATITUDE + " real not null, " +
					PoiTableMeta.KEY_LONGITUDE + " real not null, " +
					PoiTableMeta.KEY_ALTITUDE + " real not null, " +
					PoiTableMeta.KEY_FRIENDLYNAME + " text, " +
					PoiTableMeta.KEY_EXCURSIONID + " long not null, " +
					"FOREIGN KEY(" + PoiTableMeta.KEY_EXCURSIONID + ") REFERENCES " + ExcursionTableMeta.DATABASE_TABLE + "(" + ExcursionTableMeta.KEY_ROWID + ") ON DELETE CASCADE ON UPDATE CASCADE" +
			");";
	
	
	
	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(SQL_CREATE_EXCURSION_TABLE);
		db.execSQL(SQL_CREATE_EXCURSIONCOORD_TABLE);
		db.execSQL(SQL_CREATE_PHOTO_TABLE);
		db.execSQL(SQL_CREATE_POI_TABLE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(TAG, "Upgrading database from version " + oldVersion
				+ " to "
				+ newVersion + ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS " + ExcursionTableMeta.DATABASE_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + ExcursionCoordinateTableMeta.DATABASE_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + PhotoTableMeta.DATABASE_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + PoiTableMeta.DATABASE_TABLE);
		onCreate(db);
	}

}
